import threading
import time as tm
import csv
from datetime import datetime
import MySQLdb  # 或者使用 pymysql
import MySQLdb.cursors
import os

# 模拟传感器对数据库的输入
# 数据库配置
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'SensorDB',
    'charset': 'utf8mb4'
}

def get_db_connection():
    """建立并返回数据库连接"""
    try:
        connection = MySQLdb.connect(
            host=db_config['host'],
            user=db_config['user'],
            passwd=db_config['password'],
            db=db_config['database'],
            charset=db_config['charset'],
            cursorclass=MySQLdb.cursors.DictCursor  # 使用字典游标
        )
        return connection
    except MySQLdb.Error as e:
        print(f"MySQL Error: {e}")
        return None

def read_sensor_list(file_path):
    """读取 list.txt 文件并返回传感器名称列表"""
    if not os.path.exists(file_path):
        print(f"文件 {file_path} 不存在。")
        return []
    with open(file_path, 'r', encoding='utf-8') as f:
        sensors = [line.strip() for line in f if line.strip()]
    return sensors

def convert_time_str(time_str,last_time_str):
    """
    将原始时间字符串转换为目标时间格式。
    
    参数:
    - time_str: 原始时间字符串，格式为 '月/日/年 时:分.秒'，例如 '10/9/17 00:00.0'
    
    返回:
    - 转换后的时间字符串，格式为 'YYYY-MM-DD HH:MM:SS'，例如 '2017-10-09 00:00:00'
    """
    # 解析原始时间字符串
    try:
        original_time = datetime.strptime(time_str, '%m/%d/%y %M:%S.%f')
        last_origin_time = datetime.strptime(last_time_str, '%m/%d/%y %M:%S.%f')
    except ValueError as e:
        print(f"时间格式错误: {time_str}. 错误信息: {e}")
        return None
    # print(original_time.minute)
    if (original_time.minute==0 and last_origin_time.minute ==59):
        original_time.hour = original_time.hour+1
    if (original_time.hour == 24):
        original_time.hour = 0


    # 构建目标时间字符串
    target_time_str = original_time.strftime('%Y-%m-%d %H:%M:%S')
    return target_time_str,time_str

def monitor_and_insert(csv_path, list_path):
    connection = get_db_connection()
    if connection is None:
        return

    cursor = connection.cursor()
    sensors = read_sensor_list(list_path)

    # 打开 CSV 文件并逐行读取
    try:
        with open(csv_path, 'r', encoding='utf-8') as csvfile:
            reader = csv.DictReader(csvfile)
            headers = reader.fieldnames
            insert_query = """
            INSERT INTO Sensors (SensorName, Time, Data, State)
            VALUES (%s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                Data = VALUES(Data),
                State = VALUES(State)
            """
            lasthour="10/9/17 00:00.0"
            for row in reader:
                tm.sleep(1) # 不要太快
                time_str = f"{row['Date']} {row['Time']}"
                time,lasthour = convert_time_str(time_str,lasthour)
                #现在：10/9/17 00:00.0 月日年分秒
                #需要：2023-10-05 14:30:00
                # try:
                #     time = datetime.strptime(time_str, '%Y-%m-%d %H:%M:%S')  # 根据实际格式调整
                #     if time.year < 100:
                #         time = time.replace(year=time.year + 2000)
                # except ValueError:
                #     print(f"时间格式错误: {time}")
                #     continue

                for sensor in sensors:
                    data = row.get(sensor)
                    if data is None:
                        print(f"缺少传感器数据: {sensor}")
                        continue
                    try:
                        data_float = float(data)
                    except ValueError:
                        print(f"数据格式错误: {data} for sensor {sensor}")
                        continue

                    # state = row.get('State')  # 根据实际情况调整
                    # if state == '':
                    #     state = None
                    state = None # 这里不提供state
                    cursor.execute(insert_query, (sensor, time, data_float, state))
                connection.commit()

            print("数据插入完成")
    except Exception as e:
        print(f"读取 CSV 文件或插入数据时出错: {e}")
    finally:
        cursor.close()
        connection.close()

def main():
    
    list_file = 'list.txt'
    csv_file = 'WADI_attackdataLABLE.csv'
    monitor_and_insert(csv_file, list_file)
    # 启动监控线程
    # thread = threading.Thread(target=monitor_and_insert, args=(csv_file, list_file))
    # thread.daemon = True  # 设置为守护线程，主程序退出时线程也会退出
    # thread.start()

    # print("开始监控并插入数据。按 Ctrl+C 退出。")
    # try:
    #     while True:
    #         time.sleep(10)  # 主线程保持运行
    # except KeyboardInterrupt:
    #     print("程序终止。")

if __name__ == '__main__':
    main()